package com.imonsoft.sqm.service.excel.EnvManageExcel;

import java.io.File;
import java.io.FileOutputStream;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import jxl.Workbook;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import com.imonsoft.common.constant.ConstantDict;
import com.imonsoft.common.constant.ConstantSqm;
import com.imonsoft.common.exception.SystemException;
import com.imonsoft.common.util.DateUtil;
import com.imonsoft.common.util.PDFUtil;
import com.imonsoft.common.util.SendMail;
import com.imonsoft.common.util.StringUtil;
import com.imonsoft.sqm.pojo.basedata.Supplier;
import com.imonsoft.sqm.pojo.business.supplier_quality.EnvManage;
import com.imonsoft.sqm.service.basedata.DictionaryService;
import com.imonsoft.sqm.service.basedata.SupplierService;
import com.imonsoft.sqm.service.business.supplier_quality.EnvManageService;
import com.imonsoft.sqm.service.excel.BaseExcel.ExcelService;

@Service("envManageExcelService")
public class EnvManageExcelServiceImpl implements EnvManageExcelService {
	@Autowired
	private EnvManageService envManageService;
	@Autowired
	private ExcelService excelService;
	@Autowired
	private SupplierService supplierService;
	@Autowired
	private DictionaryService dictionaryService;

	/**
	 * 导出
	 * 
	 * @param id
	 * @return
	 */
	@Override
	public void getWorkBook(String[] ids, HttpServletResponse response) {
		int HEAD_ROWS = 2;
		List<EnvManage> envManageList = new ArrayList<EnvManage>();
		if (ids != null && ids.length > 0) {
			for (String id : ids) {
				EnvManage envManage = envManageService.get(Long.parseLong(StringUtil.fixup(id)));
				envManageList.add(envManage);
			}
		} else {
			List<EnvManage> allList = envManageService.queryAll();
			if (allList != null && allList.size() > 0) {
				for (EnvManage e : allList) {
					if (!e.getState().equals(ConstantSqm.STATUS_DELETE)) {
						envManageList.add(e);
					}
				}
			}
		}
		HSSFWorkbook workbook = new HSSFWorkbook();
		HSSFSheet sheet = workbook.createSheet();
		HSSFCellStyle style = workbook.createCellStyle();
		style.setLocked(false);
		style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		style.setBorderRight(HSSFCellStyle.BORDER_THIN);
		style.setBorderTop(HSSFCellStyle.BORDER_THIN);
		style.setAlignment(HSSFCellStyle.ALIGN_CENTER);

		HSSFFont fontHeader = workbook.createFont();
		HSSFFont fontTitle = workbook.createFont();

		fontHeader.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		fontHeader.setFontHeightInPoints((short) 18);

		fontTitle.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		fontTitle.setFontHeightInPoints((short) 12);

		HSSFCellStyle headerStyle = workbook.createCellStyle();
		headerStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
		headerStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		headerStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
		headerStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		headerStyle.setFont(fontHeader);
		headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		headerStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		headerStyle.setWrapText(false);

		HSSFCellStyle titleStyle = workbook.createCellStyle();
		titleStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
		titleStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		titleStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
		titleStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		titleStyle.setFont(fontTitle);
		titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		titleStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		titleStyle.setWrapText(false);

		HSSFCellStyle styleNumber = workbook.createCellStyle();
		// HSSFDataFormat df = workbook.createDataFormat();
		styleNumber.setBorderRight(HSSFCellStyle.BORDER_THIN);
		styleNumber.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		styleNumber.setBorderTop(HSSFCellStyle.BORDER_THIN);
		styleNumber.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		styleNumber.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
		styleNumber.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.000"));

		sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 8));
		addCellValue(sheet, 0, 0, "环境体系", headerStyle);
		sheet.getRow(0).setHeight((short) 600);

		addCellValue(sheet, 1, 0, "供应商编码", titleStyle);
		addCellValue(sheet, 1, 1, "供应商名称 ", titleStyle);
		addCellValue(sheet, 1, 2, "得分", titleStyle);
		addCellValue(sheet, 1, 3, "认证类型 ", titleStyle);
		addCellValue(sheet, 1, 4, "认证日期", titleStyle);
		addCellValue(sheet, 1, 5, "到期日期", titleStyle);
		addCellValue(sheet, 1, 6, "请求续订 ", titleStyle);
		addCellValue(sheet, 1, 7, "评论", titleStyle);
		if (envManageList != null && envManageList.size() > 0) {
			for (int i = 0; i < envManageList.size(); i++) {
				EnvManage envManage = envManageList.get(i);
				addCellValue(sheet, HEAD_ROWS + i, 0, envManage.getSupplierCode(), style);
				addCellValue(sheet, HEAD_ROWS + i, 1, envManage.getSupplierName(), style);
				addCellValue(sheet, HEAD_ROWS + i, 2, StringUtil.fixup(envManage.getValue()), style);
				addCellValue(sheet, HEAD_ROWS + i, 3, envManage.getCertificationType(), style);
				addCellValue(sheet, HEAD_ROWS + i, 4, StringUtil.fixup(DateUtil.date2string(envManage.getCertificationDate())), styleNumber);
				addCellValue(sheet, HEAD_ROWS + i, 5, StringUtil.fixup(DateUtil.date2string(envManage.getExpiredDate())), styleNumber);
				addCellValue(sheet, HEAD_ROWS + i, 6, envManage.getUpdateRequired(), styleNumber);
				addCellValue(sheet, HEAD_ROWS + i, 7, envManage.getComment(), styleNumber);

			}
		}
		sheet.setColumnWidth(0, 6000);
		sheet.setColumnWidth(1, 4000);
		sheet.setColumnWidth(2, 4000);
		sheet.setColumnWidth(3, 4000);
		sheet.setColumnWidth(4, 4000);
		sheet.setColumnWidth(5, 4000);
		sheet.setColumnWidth(6, 4000);
		sheet.setColumnWidth(7, 4000);
		sheet.setColumnWidth(8, 6000);
		try {
			response.reset();
			response.setContentType("aplication/msexcel"); // 设置文件类型
			String fileName = ConstantSqm.EXCEL_ENVMANAGE;// 设置名称
			response.setHeader("content-disposition", "attachment; filename=" + fileName);
			workbook.write(response.getOutputStream());
			response.flushBuffer();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	/**
	 * 添加行
	 * 
	 * @param sheet
	 * @param row
	 * @param colomn
	 * @param value
	 * @param style
	 */
	public static void addCellValue(HSSFSheet sheet, int row, int colomn, String value, HSSFCellStyle style) {
		if (sheet.getRow(row) == null) {
			sheet.createRow(row);
		}
		if (sheet.getRow(row).getCell(colomn) == null) {
			sheet.getRow(row).createCell(colomn);
		}
		sheet.getRow(row).getCell(colomn).setCellValue(value == null || "null".equals(value) ? "" : value);
		sheet.getRow(row).getCell(colomn).setCellStyle(style);
	}

	/**
	 * 加边框
	 * 
	 * @param sheet
	 * @param row
	 * @param column
	 * @param style
	 */
	public static void addborder(HSSFSheet sheet, int row, int column, HSSFCellStyle style) {
		for (int i = 1; i <= row; i++) {
			if (sheet.getRow(i) == null) {
				sheet.createRow(i);
				sheet.getRow(i).setHeight((short) 350);
			}
			for (int j = 0; j <= column; j++) {
				if (null == sheet.getRow(i).getCell(j)) {
					sheet.getRow(i).createCell(j);
				}
				sheet.getRow(i).getCell(j).setCellStyle(style);
			}
		}
	}

	@Override
	public List<EnvManage> getEnvManageList(String ids) {
		List<EnvManage> envManageList = new ArrayList<EnvManage>();
		if (ids != null && StringUtil.isNotEmpty(ids)) {
			String[] getId = ids.split(",");
			for (int i = 0; i < getId.length; i++) {
				EnvManage envManage = envManageService.get(Long.parseLong(StringUtil.fixup(getId[i])));
				if (envManage != null && envManage.getState().equals(ConstantSqm.STATUS_NORMAL)) {
					envManageList.add(envManage);
				}
			}
		} else {
			List<EnvManage> envManageAllList = envManageService.queryAll();
			if (envManageAllList != null && envManageAllList.size() > 0) {
				for (int i = 0; i < envManageAllList.size(); i++) {
					EnvManage envManage = envManageAllList.get(i);
					if (envManage != null && envManage.getState().equals(ConstantSqm.STATUS_NORMAL)) {
						envManageList.add(envManage);
					}
				}
			}
		}
		return envManageList;
	}
	@Override
	public void txExportExcel(HttpServletRequest request, Long id, HttpServletResponse response) {
		    createExcel(request, id);
			excelService.loadExcelModel(request, response, ConstantSqm.EXCEL_OUT_ENVMANAGE);
	}

	public void createExcel(HttpServletRequest request, Long id
			) {
		String errorMsg = "";
		try {
			File tFile = new File(request.getSession().getServletContext()
					.getRealPath("/excelModel/")
					+ "/" + ConstantSqm.EXCEL_ENVMANAGE);// 创建模板文件对象
			File outFile = new File(request.getSession().getServletContext()
					.getRealPath("/excelModel/")
					+ "/" + ConstantSqm.EXCEL_OUT_ENVMANAGE);// 创建输出文件对象
			// 创建文件输出流对象
			FileOutputStream os = new FileOutputStream(outFile);
			// 模板工作簿对象
			Workbook tBook = Workbook.getWorkbook(tFile);
			// 输出工作簿对象
			WritableWorkbook wbook = Workbook.createWorkbook(os, tBook);
			WritableSheet sheet = wbook.getSheet(0);
			sheet.setName("质量体系");
			WritableCellFormat cellFormat = new WritableCellFormat();
			cellFormat.setBorder(Border.ALL, BorderLineStyle.THIN);
			// 设置对齐方式
			cellFormat
					.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
			cellFormat.setAlignment(jxl.format.Alignment.LEFT);
			// 设置字体
			WritableFont bodyFont = new WritableFont(WritableFont.ARIAL, 12);
			cellFormat.setFont(bodyFont);
			// 第x列，第y行
			EnvManage envManage = envManageService.get(id);
			if (envManage != null) {
				// 供应商数据
				String linkMan = "";
				if (envManage.getSupplierId() != null) {
					Supplier supplier = supplierService.get(Long
							.parseLong(StringUtil.fixup(envManage
									.getSupplierId())));
					Label label_reason1 = new Label(2, 2,
							supplier.getLinkman(), cellFormat);
					sheet.addCell(label_reason1);// 公司
					Label label_reason2 = new Label(2, 3,
							supplier.getLinkman(), cellFormat);
					sheet.addCell(label_reason2);// 名称(联系人)
					linkMan = supplier.getLinkman();
					Label label_reason3 = new Label(8, 4, supplier.getPhone(),
							cellFormat);
					sheet.addCell(label_reason3);// 供应商电话
				}
				// 发送人邮箱名称
				String userEmail = dictionaryService.getNameByCodeType(
						ConstantDict.FSRYXMC, ConstantDict.MAIL);
				// 邮箱发送人名称
				String username = dictionaryService.getNameByCodeType(
						ConstantDict.YXFSRMC, ConstantDict.MAIL);
				Label label_reason5 = new Label(2, 7,
						StringUtil.fixup(username), cellFormat);
				sheet.addCell(label_reason5);// 姓名Name

				Label label_reason6 = new Label(2, 10, userEmail, cellFormat);
				sheet.addCell(label_reason6);// 邮件Email
				Label label_reason7 = new Label(2, 11,
						StringUtil.fixup(DateUtil.date2string(new Date())),
						cellFormat);
				sheet.addCell(label_reason7);// 日期
				Label label_reason8 = new Label(0, 17, "至供应商环境联系人" + linkMan
						+ "", cellFormat);
				sheet.addCell(label_reason8);// 供应商联系人

				Label label_reason9 = new Label(
						0,
						18,
						"您的环境体系证书于"
								+ StringUtil.fixup(DateUtil
										.date2string(new Date())
										+ "过期Your quality certifications EXPIRED last"),
						cellFormat);
				sheet.addCell(label_reason9);// 过期日期
			}
			wbook.write();
			wbook.close();
			tBook.close();
			// 关闭文件输出流
			os.close();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (StringUtil.isNotEmpty(errorMsg)) {
				throw new SystemException("生成EXCEL出错");
			}
		}
	}
	/**
	 * 为邮件生成PDF
	 */
	public void exportPdf(HttpServletRequest request, String id,
			HttpServletResponse response) {
		    File deleteFile =new File(request.getSession().getServletContext()
				.getRealPath("/excelModel/")
				+ "/" + ConstantSqm.PDF_OUT_ENVMANAGE);
				if(deleteFile.exists()){
					deleteFile.delete();
				}
		    createExcel(request, Long.parseLong(StringUtil.fixup(id)));
			convertExcelToPdf(request.getSession().getServletContext()
					.getRealPath("/excelModel/")
					+ "/" + ConstantSqm.EXCEL_OUT_ENVMANAGE, request.getSession().getServletContext()
					.getRealPath("/excelModel/")
					+ "/" + ConstantSqm.PDF_OUT_ENVMANAGE);
	}
	public void convertExcelToPdf(String filePath,String outFile){
		PDFUtil pu=new PDFUtil();
		pu.excelToPDF(filePath, outFile);
	}
	/**
	 * 环境体系发送邮件
	 * @param qualitySystem
	 * @param user
	 * @param request
	 */
	@Override
	public void txSendMail(HttpServletRequest request,HttpServletResponse response,String id,String toName,String toMail){
		//************************四个参数为系统默认邮箱参数，如需修改请到数据字典维护***************************
		// 邮箱服务器名称
		String servicename = dictionaryService.getNameByCodeType(ConstantDict.YXFWQMC, ConstantDict.MAIL);
		// 发送人邮箱名称
		String userEmail = dictionaryService.getNameByCodeType(ConstantDict.FSRYXMC, ConstantDict.MAIL);
		// 邮箱发送密码
		String password = dictionaryService.getNameByCodeType(ConstantDict.FSRYXMM, ConstantDict.MAIL);
		// 邮箱发送人名称
		//String username = dictionaryService.getNameByCodeType(ConstantDict.YXFSRMC, ConstantDict.MAIL);
		String title="环境体系过期提醒";
		exportPdf(request, id, response);
		EnvManage envManage=envManageService.get(Long.parseLong(StringUtil.fixup(id)));
		envManage.setMailDate(DateUtil.date2string(new Date()));
		envManageService.update(envManage);
		String mailbody=toName+"</br>您好：</br>贵公司的环境体系认证证书已经过期，详情请见附件";
		SendMail.sendMail(mailbody, servicename, userEmail, password, title, toMail, request.getSession().getServletContext()
				.getRealPath("/excelModel/")
				+ "/"+ConstantSqm.PDF_OUT_ENVMANAGE);
		File deleteFile =new File(request.getSession().getServletContext()
		.getRealPath("/excelModel/")
		+ "/" + ConstantSqm.PDF_OUT_ENVMANAGE);
		if(deleteFile.exists()){
			deleteFile.delete();
		}
	}
	
	/**
	 * 向所有过期的供应商发送邮件，不需要参数
	 */
	@Override
	public void txSendMailByDateOut(HttpServletRequest request,HttpServletResponse response){
		int sendCount=0;
		List<EnvManage> envManageList=envManageService.queryAll();
		if(envManageList==null){
			throw new SystemException("环境体系数据不存在");
		}
		for (EnvManage e:envManageList) {
			Date date=new Date();
			if(null!=e.getExpiredDate()&&e.getExpiredDate().before(date)&&e.getState().equals(ConstantSqm.STATUS_NORMAL)){
				if(StringUtil.isEmpty(e.getMailDate())||((e.getMailDate()!=null&&DateUtil.string2date(e.getMailDate()).before(e.getExpiredDate())))){
					Supplier supplier=supplierService.get(Long.parseLong(StringUtil.fixup(e.getSupplierId())));
					txSendMail(request, response, StringUtil.fixup(e.getId()),supplier.getName(),supplier.getMail());
					sendCount++;
				}
			}
		}
		if(sendCount==0){
			throw new SystemException("没有存在过期的供应商数据");
		}
	}
	/**
	 * 只为页面导出,jsp展示内容
	 */
	public void txCreateJustExport(HttpServletRequest request, Long id,
			HttpServletResponse response){
		try {
			File tFile = new File(request.getSession().getServletContext()
					.getRealPath("/excelModel/")
					+ "/" + ConstantSqm.EXCEL_ENVMANAGE_EXCEL);// 创建模板文件对象
			File outFile = new File(request.getSession().getServletContext()
					.getRealPath("/excelModel/")
					+ "/" + ConstantSqm.EXCEL_ENVMANAGE_EXCEL_OUT);// 创建输出文件对象
			// 创建文件输出流对象
			FileOutputStream os = new FileOutputStream(outFile);
			// 模板工作簿对象
			Workbook tBook = Workbook.getWorkbook(tFile);
			// 输出工作簿对象
			WritableWorkbook wbook = Workbook.createWorkbook(os, tBook);
			WritableSheet sheet = wbook.getSheet(0);
			sheet.setName("环境体系");
			WritableCellFormat cellFormat = new WritableCellFormat();
			// 设置对齐方式
			cellFormat.setVerticalAlignment(jxl.format.VerticalAlignment.TOP);
			cellFormat.setAlignment(jxl.format.Alignment.LEFT);
			// 设置字体
			WritableFont bodyFont = new WritableFont(WritableFont.ARIAL, 12);
			cellFormat.setFont(bodyFont);
			// 第x列，第y行
			EnvManage envManage=envManageService.get(id);
			if(envManage!=null){
				Supplier supplier=supplierService.get(Long.parseLong(StringUtil.fixup(envManage.getSupplierId())));
				Label label_reason1 = new Label(3, 5,
						supplier.getCode(), cellFormat);
				sheet.addCell(label_reason1);//供应商编号
				Label label_reason2 = new Label(3, 6,
						supplier.getName(), cellFormat);
				sheet.addCell(label_reason2);//供应商名称
				Label label_reason3 = new Label(9, 9,
						StringUtil.fixup(envManage.getValue()), cellFormat);
				sheet.addCell(label_reason3);//得分
				Label label_reason4 = new Label(9, 11,
						StringUtil.fixup(DateUtil.date2string(envManage.getCertificationDate())), cellFormat);
				sheet.addCell(label_reason4);//认证日期
				Label label_reason5 = new Label(9, 15,
						StringUtil.fixup(DateUtil.date2string(envManage.getCertificationDate())), cellFormat);
				sheet.addCell(label_reason5);//过期日期
				Label label_reason6 = new Label(1, 18,
						envManage.getComment(), cellFormat);
				sheet.addCell(label_reason6);//评论
				System.out.println(StringUtil.fixup(DateUtil.date2string(envManage.getCertificationDate())));
			}
			wbook.write();
			wbook.close();
			tBook.close();
			os.close();
		} catch (Exception e) {
			System.out.println(e.getMessage());
			e.printStackTrace();
		}
	}
	/**
	 * 下载生成后的EXCEL
	 */
	@Override
	public void txLoadJustExcel(HttpServletRequest request, Long id,
			HttpServletResponse response){
		txCreateJustExport(request, id, response);
		excelService.loadExcelModel(request, response,
				ConstantSqm.EXCEL_ENVMANAGE_EXCEL_OUT);
	}
}
